using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace RwotaStatistic.LuaToMssql
{
	public class DataTableDao
	{
		private readonly string _tableName;

		private readonly DataTable _dataTable;

		private readonly string _connectionString;

		public DataTableDao(string tableName, DataTable dataTable, string connectionString)
		{
			_tableName = tableName;
			_dataTable = dataTable;
			_connectionString = connectionString;
		}

		public void CreateTable()
		{
			using (var con = new SqlConnection(_connectionString))
			{
				var query = new StringBuilder("CREATE TABLE [dbo].[" + _tableName + "](");
				for (int i = 0; i < _dataTable.Columns.Count; i++)
				{
					DataColumn column = _dataTable.Columns[i];
					query.AppendFormat("[{0}] [nvarchar](max) null", column.ColumnName);
					if (i != _dataTable.Columns.Count - 1)
					{
						query.Append(",");
					}
				}
				query.Append(")");
				using (var cmd = new SqlCommand(query.ToString(), con))
				{
					con.Open();
					cmd.ExecuteNonQuery();
				}
			}

		}

		public void Save()
		{
			using (var con = new SqlConnection(_connectionString))
			{
				var query = new StringBuilder("insert into [dbo].[" + _tableName + "](");
				for (int i = 0; i < _dataTable.Columns.Count; i++)
				{
					DataColumn column = _dataTable.Columns[i];
					query.AppendFormat("[{0}]", column.ColumnName);
					if (i != _dataTable.Columns.Count - 1)
					{
						query.Append(",");
					}
				}
				query.Append(")");

				query.Append("values (");
				for (int i = 0; i < _dataTable.Columns.Count; i++)
				{
					query.AppendFormat("@p{0}", i);
					if (i != _dataTable.Columns.Count - 1)
					{
						query.Append(",");
					}
				} 
				query.Append(")");

				var sql = query.ToString();

				con.Open();
				var transaction = con.BeginTransaction();
				
				foreach (DataRow row in _dataTable.Rows)
				{
					using (var cmd = new SqlCommand(sql, con, transaction))
					{
						for (int i = 0; i < _dataTable.Columns.Count; i++)
						{
							cmd.Parameters.AddWithValue("@p" + i, row[i]);
						}
						cmd.ExecuteNonQuery();
					}
				}
				transaction.Commit();
			}
		}
	}
}